2014-02-03 - 9760.300 - Spec - Resource EPM reports #CrystalReportDevelopment

SPECIFICATIONS

9760.300 - Resource EPM reports


Requirement Summary

Need to create new EPM250 - Monthly Scorecard as a forward looking 3 month report from new Multiprovider.

Admin Info

Purpose
 Need to create new EPM250 - Monthly Scorecard as a forward looking 3 month report from new Multiprovider.
Requested By
 Mark Harris
Spec Created By
 Praveen Guntuka
Spec Created Date
 02/03/2014
Spec QA by
 Uday Kumar P
Objects
 EPM250
Document Status
 Complete

References

Prior Tickets

None

Documents

None

Functional Requirement

1. New EPM250 Scorecard by Monthly should show Shipped, Last Year, Budget, Last Year Variance and Budget variance for YTD and Open, Total, Last Year and Budget for Total Year and TY, LY and Budget Contribution % for S YTD and SO Full Year in Main section.
2. In all subreports it should show Total(Shipped + Open), Budget, Budget Variance,and Total, Budget and Budget Variance Contribution % for next 3 months based on Month parameter value.
3. Need to display Product Mix, License, Customer Segment, Customer, Silhouette and Collection wise data for above mentioned columns.
4. Need to display Report Name, Region Name, Currency, Units, Period and Year in header section.
5. Need to remove all the Budget files from the report.
6. Need to set the ‘TOP N’ for product Mix and all values for Silhouette, Customer, Customer segment & License sub reports.
7. For License Subreport, we have to display all licenses that have budget values for 3 months and move the other licenses without having budget for 3 months and License name is 'Other' into ‘Others’ category. Next we have sort the licenses based on descending order of First Month Total field value.
8. For Customer Segment subreport, we have to show all the Customer Segments.
9. For Customer subreport, we need display all customers that are not in between "9100001" to "9100009" and move the "9100001" to "9100009" customers into 'Others' category. We have to sort the Customers based on Customer Name.
10. For Sillhouette subreport, we need to display all the silhouettes that have budget values for 3 months and move the other Silhouettes without having budget for 3 months and Silhouette name is 'Other' into ‘Others’ category. Next we have sort the Silhouettes based on descending order of First Month Total field value.
11. For Collection subreport, we have to show Top 5 Collection values and move rest of Collections into 'Others' section as we do not have Budget values for Collections in database.

Solution Summary

1. Need to remove the Budget files from the EPM250 report.
2. Need to update the report with new BEx queries.
3. Need to create formulas to show Shipped, Last Year, Budget, Last Year Variance and Budget variance for YTD and Open, Total, Last Year and Budget for Total Year and TY, LY and Budget Contribution % for S YTD and SO Full Year in Main section.
4. Need to create formulas to show total(Shipped + Open), Budget, Budget Variance,and Total, Budget and Budget Variance Contribution % for next 3 months based on Month parameter value.
5. Need to display Report Name, Region Name, Currency, Units, Period and Year in header section.
6. Need to set the ‘TOP N’ for product Mix and all values for Silhouette, Customer, Customer segment & License sub reports.
7. For License Subreport, we have to display all licenses that have budget values for 3 months and move the other licenses without having budget for 3 months and License name is 'Other' into ‘Others’ category. Next we have sort the licenses based on descending order of First Month Total field value.
8. For Customer Segment subreport, we have to show all the Customer Segments.
9. For Customer subreport, we need display all licenses that are not in between "9100001" to "9100009" and move the "9100001" to "9100009" Customers in to 'Others' category. We have to sort the Customers based on Customer Name.
10. For Sillhouette subreport, we need to display all the silhouettes that have budget values for 3 months and move the other Silhouettes without having budget for 3 months and Silhouette name is 'Other' into ‘Others’ category. Next we have sort the Silhouettes based on descending order of First Month Total field value.
11. For Collection subreport, we have to show Top 5 Collection values and move rest of Collections to 'Others' section as we do not have Budget values for Collections in database.

Test Plan

SNo.
 Test Scenario
 Expected Results
1
 Execute the new Crystal Report in Crystal Designer tool after changes.
 Report should execute successfully in Crystal Designer tool.
2
 Schedule the Crystal Report in NEC Infoview.
 Report should execute successfully in NEC Infoview.
3
 Validate the values with BEx query values.
 Report should successfully validate with BEx query values.

Solution Details

Need to update the report with new BEx queries and make the following report changes:

Main report section:

1. Need to remove the Budget files form the main report.
2. For YTD section, need to display Shipped, Last Year, Budget, Last Year Variance and Budget variance and Coverage % for Last Year and Budget.
3. For Total Year section, Need to display Open, Total, Last Year and Budget and Coverage % for Last year and Budget.
4. For Contribution % section, need to display TY, LY and Budget Contribution % for S YTD and SO Full Year.
For S YTD column:
For SO Full Year column:
5. Each subreport need to show four sections as follows:

SubReport Details:

Product Mix Subreport:
1. Need to show Total(Open + Shipped), Budget, Budget Variance for 3 months based on Month parameter value and Contribution % for Total, Budget and Budget variance for respective Product Mix.
2. Need to set the ‘TOP 5’ for product Mix and sort descending based on first Month Total value.

License Subreport:
1. Need to show Total(Open + Shipped), Budget, Budget Variance for 3 months based on Month parameter value and Contribution % for Total, Budget and Budget variance for respective Licenses.
2. Need to display all licenses that have budget values for 3 months.
3. Need to display Licenses without having budget for 3 months and License name is 'Other' into ‘Others’ category.
4. Need to sort the licenses based on descending order of First Month Total field value.

Customer Segment Subreport:
1. Need to show Total(Open + Shipped), Budget, Budget Variance for 3 months based on Month parameter value and Contribution % for Total, Budget and Budget variance for respective Customer Segment values.
2. Need to display all Customer Segments by region.
3. Need to sort the Customer Segments based on Ascending order of Customer Segment Key value.

Customer Subreport:
1. Need to show Total(Open + Shipped), Budget, Budget Variance for 3 months based on Month parameter value and Contribution % for Total, Budget and Budget variance for respective Customer values.
2. Need to display all Customers that are not in between "9100001" to "9100009".
3. Need to display "9100001" to "9100009" Customers in to 'Others' category.
4. Need to sort the Customers based on ascending order of Customer Names.

Silhouette Subreport:
1. Need to show Total(Open + Shipped), Budget, Budget Variance for 3 months based on Month parameter value and Contribution % for Total, Budget and Budget variance for respective Silhouettes.
2. Need to display all Silhouettes that have budget values for 3 months.
3. Need to display Silhouettes without having budget for 3 months and Silhouettes name is 'Other' into ‘Others’ category.
4. Need to sort the licenses based on descending order of First Month Total field value.

Collection Subreport:
1. Need to show Total(Open + Shipped), Budget, Budget Variance for 3 months based on Month parameter value and Contribution % for Total, Budget and Budget variance for respective Collections.
2. Need to set the ‘TOP 5’ for Collections and move rest of Collections to 'Others' category, as we do not have Budget values for Collections in database.

Formulas used in Subreports:

Issues

None